package com.zl.learning.db.dbutil;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by zhaolei on 2017/6/30.
 */
public class Test1 {

    public static QueryRunner queryRunner = new QueryRunner(ConnTools.getDataSource());

    @Test
    public void test_insert() throws SQLException {
        //创建SQL执行工具
        User u = new User();
        u.setName("zhaosi");
        u.setPswd("qq");
        Long n = queryRunner.insert("insert into tbl_user(name,pswd) values(?,?)", new ScalarHandler<Long>("GENERATED_KEY"), u.getName(), u.getPswd());
        //执行SQL插入
//        int n = qRunner.update(conn, "insert into tbl_user(name,pswd) values('iii','iii')");
        System.out.println("插入成功 新数据ID:" + n);
    }

    @Test
    public void test_find() throws SQLException {
        System.out.println("-------------test_find()-------------");
        //创建连接
        Connection conn = ConnTools.getConnection();
        //创建SQL执行工具
        QueryRunner qRunner = new QueryRunner();

        //执行SQL查询，并获取结果
        List<User> list = qRunner.query(conn, "select user_id id,skxm name,password pswd from tp_users", new BeanListHandler<User>(User.class));
        //输出查询结果
        for (User user : list) {
            System.out.println(user);
        }
        //关闭数据库连接
        DbUtils.closeQuietly(conn);
    }

    @Test
    public void test1() throws SQLException {
        System.out.println("-------------test_find()-------------");
        //创建连接
        Connection conn = ConnTools.getConnection();
        //创建SQL执行工具
        QueryRunner qRunner = new QueryRunner();

        //执行SQL查询，并获取结果
        List<Integer> list = qRunner.query(conn, "select id  from tbl_user", new AbstractListHandler<Integer>() {
            @Override
            protected Integer handleRow(ResultSet rs) throws SQLException {
                return rs.getInt(1);
            }
        });
        //输出查询结果
        for (Integer user : list) {
            System.out.println(user);
        }
        //关闭数据库连接
        DbUtils.closeQuietly(conn);
    }

    @Test
    public void test2() throws IntrospectionException {
        BeanInfo beanInfo = Introspector.getBeanInfo(User.class);

        PropertyDescriptor[] props = beanInfo.getPropertyDescriptors();
        for (PropertyDescriptor prop : props) {
            System.out.println(prop.getPropertyType() + "::" + prop.getName());
        }
//        System.out.println(props[0].getPropertyType());
    }

    @Test
    public void test3() throws SQLException {
        Map<String, Map<String, Object>> ms = queryRunner.query("select * from tp_users", new KeyedHandler<String>("password"));
        System.out.println(ms);
    }

    @Test
    public void test4() throws SQLException {
        String sql = "select * from tp_users";
        List list1 = queryRunner.query(sql, rs -> {
            List list = new ArrayList<>();
            while (rs.next()) {
                Object o = rs.getObject(4);
                list.add(o);
            }
            return list;
        });
        list1.forEach(System.out::println);
    }

    @Test
    public void test5() throws SQLException {
        String sql = "select user_money from tp_users";
        List<String> list1 = queryRunner.query(sql, new ColumnListHandler<Number>()).stream().map(Number::toString).collect(Collectors.toList());
        list1.forEach(System.out::println);
    }

    @Test
    public void test6() throws SQLException {
        String sql = "select user_money from tp_users where user_id = 11167";
        int userMoney = queryRunner.query(sql, new ScalarHandler<Number>()).intValue();
        System.out.println(userMoney);
    }

    @Test
    public void test7() throws SQLException {
        String sql = "select user_money from tp_users where user_id =1";
        Number userMoney = queryRunner.query(sql, new ScalarHandler<Number>());
        System.out.println(userMoney.byteValue());
    }

    @Test
    public void test8() throws SQLException {
        String sql = "select edit_time from tp_sms_log ";
        String userMoney = queryRunner.query(sql, new ScalarHandler<Date>()).toInstant().atZone(ZoneId.systemDefault()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        System.out.println(userMoney);
    }

    @Test
    public void test9() throws SQLException {
        String sql = "select edit_time from tp_sms_log ";
        List<String> editTimes = queryRunner.query(sql, new ColumnListHandler<Date>()).stream().map(this::dateToStr).collect(Collectors.toList());
        editTimes.forEach(System.out::println);

    }

    private String dateToStr(Date date) {
        return date != null ? date.toInstant().atZone(ZoneId.systemDefault()).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")) : "";
    }
}